/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Controller;

import Model.login;
import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Martin Spencer Sy
 */
public class FinanceManager {
    private static Connection con = null;
    private static String sql = null;
    private static ResultSet rs = null;
    
    public static ResultSet getSalesPerProduct(){      
        Statement s;
        try 
        {   Class.forName("com.mysql.jdbc.Driver");
            
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/foobar", 
                    "root", "");
            
            s = con.createStatement();
            sql = "SELECT u.name, CONCAT('Php', SUM(p.price)) FROM listUserBought u, listProduct p WHERE p.name=u.name GROUP BY u.name";
            
            rs = s.executeQuery(sql);
        }
        catch (SQLException ex) {
            //Logger.getLogger(login.class.getName()).log(Level.SEVERE, null, ex);
            LogManager.logEvent(FinanceManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught SQL Exception: " + ex.getMessage(), null, null);
        }
        catch (ClassNotFoundException e1) {
            // JDBC driver class not found, print error message to the console
            System.out.println(e1.toString());
            LogManager.logEvent(FinanceManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Class Not Found Exception: " + e1.getMessage(), null, null);
        }
        catch (Exception e3) {
            // other unexpected exception, print error message to the console
            System.out.println(e3.toString());
            LogManager.logEvent(FinanceManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Exception: " + e3.getMessage(), null, null);
        }
        
        return rs;
    }
    
    public static ResultSet getSalesPerProductType(){
        
        Statement s;

        try 
        {   

            Class.forName("com.mysql.jdbc.Driver");

            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/foobar", 
                    "root", "");
            
            s = con.createStatement();
            sql = "SELECT LP.type, CONCAT('Php', SUM(LP.price)) FROM listProduct LP, listUserBought LU WHERE LU.pid=LP.pid AND LP.type='accessories'"
                    + " UNION SELECT LP.type, CONCAT('Php', SUM(LP.price)) FROM listProduct LP, listUserBought LU WHERE LU.pid=LP.pid AND LP.type='book'"
                    + " UNION SELECT LP.type, CONCAT('Php', SUM(LP.price)) FROM listProduct LP, listUserBought LU WHERE LU.pid=LP.pid AND LP.type='cd'"
                    + " UNION SELECT LP.type, CONCAT('Php', SUM(LP.price)) FROM listProduct LP, listUserBought LU WHERE LU.pid=LP.pid AND LP.type='dvd'"
                    + " UNION SELECT LP.type, CONCAT('Php', SUM(LP.price)) FROM listProduct LP, listUserBought LU WHERE LU.pid=LP.pid AND LP.type='magazine'";

            rs = s.executeQuery(sql);
        }
        catch (SQLException ex) {
            //Logger.getLogger(login.class.getName()).log(Level.SEVERE, null, ex);
            LogManager.logEvent(FinanceManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught SQL Exception: " + ex.getMessage(), null, null);
        }
        catch (ClassNotFoundException e1) {
            // JDBC driver class not found, print error message to the console
            System.out.println(e1.toString());
            LogManager.logEvent(FinanceManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Class Not Found Exception: " + e1.getMessage(), null, null);
        }
        catch (Exception e3) {
            // other unexpected exception, print error message to the console
            System.out.println(e3.toString());
            LogManager.logEvent(FinanceManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Exception: " + e3.getMessage(), null, null);
        }
        
        return rs;
    }
    
    public static ResultSet getTotalSales(){
        
        Statement s;

        try 
        {   

            Class.forName("com.mysql.jdbc.Driver");

            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/foobar", 
                    "root", "");

            s = con.createStatement();
            sql = "SELECT CONCAT('Php', SUM(LP.price)) FROM listUserBought LU, listProduct LP where LU.pid=LP.pid";
            rs = s.executeQuery(sql);
        }
        catch (SQLException ex) {
            //Logger.getLogger(login.class.getName()).log(Level.SEVERE, null, ex);
            LogManager.logEvent(FinanceManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught SQL Exception: " + ex.getMessage(), null, null);
        }
        catch (ClassNotFoundException e1) {
            // JDBC driver class not found, print error message to the console
            System.out.println(e1.toString());
            LogManager.logEvent(FinanceManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Class Not Found Exception: " + e1.getMessage(), null, null);
        }
        catch (Exception e3) {
            // other unexpected exception, print error message to the console
            System.out.println(e3.toString());
            LogManager.logEvent(FinanceManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Exception: " + e3.getMessage(), null, null);
        }
        
        return rs;
    }
    
    public static void closeDB(){
        try {
            con.close();
            rs.close();
        } 
        catch (SQLException ex) {
            //Logger.getLogger(ProductManager.class.getName()).log(Level.SEVERE, null, ex);
            LogManager.logEvent(FinanceManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught SQL Exception: " + ex.getMessage(), null, null);
        }
    }
}
